package com.fy56.platform.goodsTracking.service.impl;

import com.dexcoder.dal.JdbcDao;
import com.dexcoder.dal.build.Criteria;
import com.fy56.platform.goodsTracking.model.*;
import com.fy56.platform.goodsTracking.service.GoodstrackedRecordTopPublicService;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by XTK on 2016-11-21.
 */
@Repository
public class GoodstrackedRecordTopPublicImpl implements GoodstrackedRecordTopPublicService {
    static Logger log = Logger.getLogger(GoodstrackedRecordTopPublicService.class);
    @Autowired
    JdbcDao jdbcDao_GoodsTracked;

    @Override
    public List<GoodstrackedRecordTopPublic> selectByOrganizationcode(String organizationcode) {
        Criteria criteria = Criteria.select(GoodstrackedRecordTopPublic.class)
                .where("organizationcode", new Object[]{organizationcode});
        return jdbcDao_GoodsTracked.queryList(criteria);
    }

    @Override
    public List<GoodstrackedRecordTopPublic> selectByNotUpload(String organizationcode, String beginTime, String endTime) {
        Criteria criteria = Criteria.select(GoodstrackedRecordTopPublic.class)
                .where("organizationcode", new Object[]{organizationcode})
                .and("Systime", ">=", new Object[]{beginTime})
                .and("Systime", "<=", new Object[]{endTime})
                .and("upload", new Object[]{null})
                .asc("systime");
        return jdbcDao_GoodsTracked.queryList(criteria);
    }

    @Override
    public List<GoodstrackedRecordTopPublic> selectByNotUpload_Police(String organizationcode, String beginTime, String endTime) {
        Criteria criteria = Criteria.select(GoodstrackedRecordTopPublic.class)
                .where("organizationcode", new Object[]{organizationcode})
                .and("Systime", ">=", new Object[]{beginTime})
                .and("Systime", "<=", new Object[]{endTime})
                .asc("systime");
        return jdbcDao_GoodsTracked.queryList(criteria);
    }
    @Override
    public List<GoodstrackedRecordTopPublicF6> selectBuUploadList(String organizationcode, String beginTime, String endTime) {
        String sql = "select o.TracePlatformId as otempid,Convert(decimal(18,2),t.totalnumberofpackages) as totalnumberofpackages," +
                "CONVERT(varchar(100), t.SysTime, 20) SYSTIME " +
                ",t.ConsignmentBillNumber " +
                ",t.GoodsNumber " +
                ",t.ManualNumber,t.* " +
                "from GoodsTracked_Record_Top_Public t WITH(NOLOCK)  " +
                "  left join Order_temp o WITH(NOLOCK) on o.ConsignmentBillMasterId=t.ConsignmentBillMasterId " +
                " where  1=1 " +
                " and t.Systime>='" + beginTime + "'" +
                " and t.Systime<='" + endTime + "'";
                if (organizationcode!=null&&organizationcode.length()!=0){
                    sql=sql+" and t.organizationcode  ='"+organizationcode+"'" ;
                }
                sql=sql+ " and t.organizationcode not  in('fyrjgs','fycsz')" +
                " and (t.uploadFlag is null or t.uploadFlag='') "+
                " and (o.TracePlatformId is null or o.TracePlatformId='') " +
                " order by t.systime asc ";
//        System.out.println("selNotUploadList:"+sql);
        List<GoodstrackedRecordTopPublicF6> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, GoodstrackedRecordTopPublicF6.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }
    @Override
    public List<GoodstrackedRecordTopPublicF6> selectNotList(String organizationcode, String beginTime, String endTime) {
        String sql = "select o.TracePlatformId as otempid,Convert(decimal(18,2),t.totalnumberofpackages) as totalnumberofpackages," +
                "CONVERT(varchar(100), t.SysTime, 20) SYSTIME " +
                ",t.ConsignmentBillNumber " +
                ",t.GoodsNumber " +
                ",t.ManualNumber,t.* " +
                "from GoodsTracked_Record_Top_Public t WITH(NOLOCK)  " +
                "  left join Order_temp o WITH(NOLOCK) on o.ConsignmentBillMasterId=t.ConsignmentBillMasterId " +
                " where  1=1 " +
                " and t.Systime>='" + beginTime + "'" +
                " and t.Systime<='" + endTime + "'" +
                " and t.organizationcode not  in('fyrjgs','fycsz')" +
                " and (t.uploadFlag is null or t.uploadFlag='') " +
                " order by t.systime asc ";
//        System.out.println("selNotUploadList:"+sql);
        List<GoodstrackedRecordTopPublicF6> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, GoodstrackedRecordTopPublicF6.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }

    @Override
    public void updateByupload(GoodstrackedRecordTopPublic top) {
        jdbcDao_GoodsTracked.updateForSql("update GoodsTracked_Record_Top_Public " +
                "set upload='" + top.getUpload()
                + "',uploadError='"
                + top.getUploaderror() + "' where id=" + top.getId());
    }

    @Override
    public void updateTyCallback(String consignmentbillmasterid,String code,String message){
        jdbcDao_GoodsTracked.updateForSql("update GoodsTracked_Record_Top_Public " +
                "set tyCallbackCode='" + code
                + "',tyCallbackMessage='"
                + message + "' where consignmentbillmasterid='" + consignmentbillmasterid+"'");
    }
    @Override
    public void updateCarCallback(String consignmentbillmasterid,String code,String message){
        jdbcDao_GoodsTracked.updateForSql("update TransportBillMaster_public " +
                "set tyCallbackCode='" + code
                + "',tyCallbackMessage='"
                + message + "' where TransportBillMasterId='" + consignmentbillmasterid+"'");
    }
    @Override
    public void updateDshkCallback(String consignmentbillmasterid,String code,String message){
        jdbcDao_GoodsTracked.updateForSql("update CollectionGoodsValueAccount_Public " +
                "set tyCallbackCode='" + code
                + "',tyCallbackMessage='"
                + message + "' where CollectionGoodsValueAccountId='" + consignmentbillmasterid+"'");
    }
    @Override
    public List<TransportbillmasterPublic> selectCarNotUpload(String organizationcode, String beginTime, String endTime) {
//        Criteria criteria = Criteria.select(GoodstrackedRecordTopPublic.class)
//                .where("Systime", ">=", new Object[]{beginTime})
//                .and("Systime", "<=", new Object[]{endTime})
//                .and("Traceplatformid",new Object[]{" is null "})
//                .asc("systime");
//        return jdbcDao_GoodsTracked.queryList(criteria);
        String sql = "select t.*  " +
                " from TransportBillMaster_public t WITH(NOLOCK)  " +
                " where  1=1 " +
                " and t.systime>='" + beginTime + "'" +
                " and t.systime<='" + endTime + "'" +
                " and t.organizationcode not in('fyrjgs','fycsz')" +
                " and (t.uploadstate is null or t.uploadstate='') " +
                " order by t.systime asc ";
//        System.out.println("selNotUploadList:"+sql);
        List<TransportbillmasterPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, TransportbillmasterPublic.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }

    @Override
    public List<TransportbillDetailsPublic> selectCarMxlist(String transportBillMasterId) {
        String sql = "select o.TracePlatformId as trackingNo,t.*  " +
                " from TransportBillDetails_Public t WITH(NOLOCK)  " +
                " left join Order_temp o WITH(NOLOCK) on  t.ConsignmentBillMasterId=o.ConsignmentBillMasterId" +
                " where  1=1 " +
                " and t.transportBillMasterId='"+transportBillMasterId+"' " +
                " order by t.systime asc ";
        List<TransportbillDetailsPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, TransportbillDetailsPublic.class);
        }catch (Exception e){
            log.error("selectCarMxlist error:",e);
        }
        return result;
    }

    @Override
    public List<CollectiongoodsvalueaccountPublic> selectCollectionMoneyNotUpload(String organizationcode, String beginTime, String endTime) {
//        Criteria criteria = Criteria.select(GoodstrackedRecordTopPublic.class)
//                .where("Systime", ">=", new Object[]{beginTime})
//                .and("Systime", "<=", new Object[]{endTime})
//                .and("Traceplatformid",new Object[]{" is null "})
//                .asc("systime");
//        return jdbcDao_GoodsTracked.queryList(criteria);
        String sql = "select t.*  " +
                "from CollectionGoodsValueAccount_Public t WITH(NOLOCK)  " +
                " where  1=1 " +
                " and t.systime>='" + beginTime + "'" +
                " and t.systime<='" + endTime + "'" +
//                " and GoodsTracked_Record_Top_Public.organizationcode='" + organizationcode + "'" +
                " and (t.flag is null or t.flag='')  " +
                " order by t.systime asc ";
//        System.out.println("selNotUploadList:"+sql);
        List<CollectiongoodsvalueaccountPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, CollectiongoodsvalueaccountPublic.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }

    @Override
    public List<CollectiongoodsvalueaccountPublic> selectCollectionMoneyNotUploadNew(String organizationcode, String beginTime, String endTime) {
        String sql = "select o.TracePlatformId as traceplatformidorder,t.*  " +
                "from CollectionGoodsValueAccount_Public t WITH(NOLOCK) " +
                "left join Order_temp o WITH(NOLOCK) on  t.ConsignmentBillMasterId=o.ConsignmentBillMasterId" +
                " where  1=1 " +
                " and t.systime>='" + beginTime + "'" +
                " and t.systime<='" + endTime + "'" +
                " and t.organizationcode not in ('fyrjgs','fycsz')" +
                " and (t.flagnew is null or t.flagnew='')  " +
                " and (o.TracePlatformId is not null and o.TracePlatformId!='')  " +
                " order by t.systime asc ";
//        System.out.println("selectCollectionMoneyNotUploadNew:"+sql);
        List<CollectiongoodsvalueaccountPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, CollectiongoodsvalueaccountPublic.class);
        }catch (Exception e){
            log.error("selectCollectionMoneyNotUploadNew error:",e);
        }
        return result;
    }
    @Override
    public List<TransportbillmasterPublic> selectCarNotUploadBC(String organizationcode, String beginTime, String endTime,int num) {
//        String sql = "select top "+num+" TransportBillMasterId, t.*  " +
//                "from TransportBillMaster_public t WITH(NOLOCK)  " +
//                " where  1=1 " +
//                " and t.systime>='" + beginTime + "'" +
//                " and t.systime<='" + endTime + "'" +
////                " and GoodsTracked_Record_Top_Public.organizationcode='" + organizationcode + "'" +
//                " and (t.uploadstate is null or t.uploadstate='') " +
//                " order by t.systime asc ";
//        String sql = "select top "+num+" t.*  " +
        String sql = "select  t.*  " +
                " from TransportBillMaster_public t WITH(NOLOCK)  " +
                " where  1=1 " +
                " and t.systime>='" + beginTime + "'" +
                " and t.systime<='" + endTime + "'" +
                " and t.organizationcode not in('fyrjgs','fycsz')" +
//                " and (t.uploadstate is null or t.uploadstate='') " +
                " order by t.systime asc ";
        List<TransportbillmasterPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, TransportbillmasterPublic.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }
    @Override
    public List<CollectiongoodsvalueaccountPublic> selectCollectionMoneyNotUploadBC(String organizationcode, String beginTime, String endTime,int num) {
//        String sql = "select top "+num+" CollectionGoodsValueAccountId t.*  " +
//                "from CollectionGoodsValueAccount_Public t WITH(NOLOCK)  " +
//                " where  1=1 " +
//                " and t.GiveDate>='" + beginTime + "'" +
//                " and t.GiveDate<='" + endTime + "'" +
//                " and (t.flagnew is null or t.flagnew='' or t.flagnew='2') " +
//                " order by t.systime asc ";


        String sql = "select o.TracePlatformId as traceplatformidorder,t.*  " +
                "from CollectionGoodsValueAccount_Public t WITH(NOLOCK) " +
                "left join Order_temp o WITH(NOLOCK) on  t.ConsignmentBillMasterId=o.ConsignmentBillMasterId" +
                " where  1=1 " +
                " and t.GiveDate>='" + beginTime + "'" +
                " and t.GiveDate<='" + endTime + "'" +
                " and t.organizationcode not in ('fyrjgs','fycsz')" +
                " and (t.flagnew is null or t.flagnew='')  " +
                " and (o.TracePlatformId is not null and o.TracePlatformId!='')  " +
                " order by t.GiveDate asc ";
        List<CollectiongoodsvalueaccountPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, CollectiongoodsvalueaccountPublic.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }
    @Override
    public List<CollectiongoodsvalueaccountPublic> dshkShanfu(String organizationcode, String beginTime, String endTime,int num) {


        String sql = "select o.TracePlatformId as traceplatformidorder,t.*  " +
                "from CollectionGoodsValueAccount_Public t WITH(NOLOCK) " +
                "left join Order_temp o WITH(NOLOCK) on  t.ConsignmentBillMasterId=o.ConsignmentBillMasterId" +
                " where  1=1 " +
                " and t.GiveDate>='2020-09-01 00:00:00'" +
                " and t.GiveDate<='2020-12-22 23:59:59'" +
                " and t.CollectionGoodsValueType='秒付通'" +
                " and (t.flagnew is null or t.flagnew='')  " +
                " and (o.TracePlatformId is not null and o.TracePlatformId!='')  " +
                " order by t.GiveDate asc ";
        List<CollectiongoodsvalueaccountPublic> result = null;
        try{
            result = jdbcDao_GoodsTracked.queryListForSql(sql, CollectiongoodsvalueaccountPublic.class);
        }catch (Exception e){
            log.error("selectNotList error:",e);
        }
        return result;
    }
}
